<ui:composition  xmlns="http://www.w3.org/1999/xhtml"
                 xmlns:h="http://java.sun.com/jsf/html"
                 xmlns:f="http://java.sun.com/jsf/core"
                 xmlns:ui="http://java.sun.com/jsf/facelets"
                 xmlns:p="http://primefaces.org/ui"
                 xmlns:x="http://xpert.com/faces"
                 template="/template/mainTemplate.xhtml">

    <ui:param name="title" value="Query Builder" />
    <ui:define name="body">


        <div class="description">
            QueryBuilder is a easy way to create JPQL. It can be created with a EntityManager
        </div>


        <h3>Create a QueryBuilder from a EntityManager</h3>
        <pre name="code" class="java">
            QueryBuilder queryBuilder = new QueryBuilder(entityManager);
        </pre>
        <h3>Create a QueryBuilder from a BaseDAO</h3>
        QueryBuilder can be created from a BaseDAO:
        <pre name="code" class="java">
            QueryBuilder queryBuilder = someDAO.getQueryBuilder();
        </pre>
        Example:
        <p:tabView>
            <p:tab title="QueryBuilderMB.java">
                <pre name="code" class="java">
                public class QueryBuilderMB {

                    @EJB
                    private PersonDAO personDAO;

                    @PostConstruct
                    public void init(){

                        QueryBuilder queryBuilder = personDAO.getQueryBuilder();    

                    }  

                }
                </pre>
            </p:tab>
            <p:tab title="PersonDAO.java">
                <ui:include src="/views/common/java/personDAO.xhtml"/>
            </p:tab>
        </p:tabView>

        <br/>
        <h3>Select all rows</h3>

        JPQL:
        <pre name="code" class="java">
        FROM Person.class
        </pre>
        Using QueryBuilder:
        <pre name="code" class="java">
                        queryBuilder.from(Person.class).getResultList();
        </pre>

        <h3>Ordering a Result</h3>
        The method "orderBy()" define the order in result
        <pre name="code" class="java">
                           queryBuilder
                                 .from(Person.class)
                                 .orderBy(&quot;name&quot;).getResultList();
        </pre>
        Multiple order (ASC and DESC can be defined): 
        <pre name="code" class="java">
                           queryBuilder
                                 .from(Person.class)
                                 .orderBy(&quot;name, code DESC&quot;).getResultList();
        </pre>
        
        <h3>Get a Single Result</h3>
        The method "getSingleResult()" calls JPA Query "getSingleResult()"
        <pre name="code" class="java">
                           queryBuilder
                                 .from(Person.class)
                                 .getSingleResult();
        </pre>

        <h3>Using Retrictions</h3>
        JPQL:
        <pre name="code" class="java">
        FROM Person.class WHERE name LIKE '%Maria%' ORDER BY name
        </pre>
        Using QueryBuilder:
        <pre name="code" class="java">
                           queryBuilder
                                 .from(Person.class)
                                 .like(&quot;name&quot;, &quot;Maria&quot;)
                                 .orderBy(&quot;name&quot;).getResultList();
        </pre>

        <h3>Alias</h3>
        The alias can be defined in "from" method
        <pre name="code" class="java">
            queryBuilder.select("p").from(Person.class, "p");
        </pre>

        Restrictions and ordering can be used with the "path" defined with the alias
        <pre name="code" class="java">
            queryBuilder.select("p")
                       .from(Person.class, "p")
                       .equals(&quot;p.name&quot;, "Peter")
                       .orderBy(&quot;p.name&quot;)
                       .getResultList();
        </pre>

        <h3>Joins</h3>
        The methods "innerJoin()", "innerJoinFetch()", "rightJoin()", "rightJoinFetch()", "leftJoin()" and "leftJoinFetch()" can be used to define joins
        <pre name="code" class="java">
        queryBuilder.select(&quot;p&quot;)
                    .from(Person.class, &quot;p&quot;)
                    .innerJoin(&quot;p.group&quot;, &quot;g&quot;)
                    .orderBy(&quot;g.description&quot;)
                    .getResultList();
        </pre>

        <h3>Select Distinct</h3>
        use "selectDistinct" method to Distinct que select clausule
        <pre name="code" class="java">
        queryBuilder
                .selectDistinct(&quot;p&quot;)
                .from(Person.class, &quot;p&quot;)
                .leftJoinFetch(&quot;p.permissions&quot;, &quot;pe&quot;)
                .getResultList();
        </pre>

        <h3>Set Max Results</h3>
        Setting a max of 10 results:
        <pre name="code" class="java">
                           queryBuilder
                                 .from(Person.class)
                                 .orderBy(&quot;name&quot;)
                                 .setMaxResults(10)
                                 .getResultList();
        </pre>


        <h3>Set First and Max Results</h3>
        Setting first 0 and max of 10 results:
        <pre name="code" class="java">
                           queryBuilder
                                 .from(Person.class)
                                 .orderBy(&quot;name&quot;)
                                 .setFirstResult(0)
                                 .setMaxResults(10)
                                 .getResultList();
        </pre>
        Setting first 10 and no max results:
        <pre name="code" class="java">
                           queryBuilder
                                 .from(Person.class)
                                 .orderBy(&quot;name&quot;)
                                 .setFirstResult(10)
                                 .getResultList();
        </pre>

        <h3>Debug Query</h3>
        To show generated query (and the parameters) just call the method "debug()"
        <pre name="code" class="java">
                           queryBuilder
                                 .from(Person.class)
                                 .orderBy(&quot;name&quot;)
                                 .debug()
                                 .getResultList();
        </pre>

        <h3>Define attributes in query</h3>
        To Define attributes in query the expected type must be defined in "getResultList()".
        <pre name="code" class="java">
        queryBuilder.select(&quot;name,code,id&quot;)
                    .from(Person.class)
                    .orderBy(&quot;name&quot;)
                    .getResultList(Person.class);
        </pre>

        <h3>Count rows</h3>
        JPQL:
        <pre name="code" class="java">
            SELECT COUNT(*) FROM Person.class 
        </pre>
        Using QueryBuilder:
        <pre name="code" class="java">
                        queryBuilder.from(Person.class).count();
        </pre>

        <h3>Sum</h3>

        JPQL:
        <pre name="code" class="java">
            SELECT SUM(salary) FROM Person.class 
        </pre>
        Using QueryBuilder:
        <pre name="code" class="java">
                        (BigDecimal) queryBuilder.from(Person.class).sum(&quot;salary&quot;);
        </pre>
        Return "BigDecimal.ZERO" wher result is null:
        <pre name="code" class="java">
                        (BigDecimal) queryBuilder.from(Person.class).sum(&quot;salary&quot;, BigDecimal.ZERO);
        </pre>
        <h3>Max</h3>
        JPQL:
        <pre name="code" class="java">
            SELECT MAX(salary) FROM Person.class 
        </pre>
        Using QueryBuilder:
        <pre name="code" class="java">
                        (BigDecimal) queryBuilder.from(Person.class).max(&quot;salary&quot;);
        </pre>

        <h3>Min</h3>
        JPQL:
        <pre name="code" class="java">
            SELECT MIN(salary) FROM Person.class 
        </pre>
        Using QueryBuilder:
        <pre name="code" class="java">
                        (BigDecimal) queryBuilder.from(Person.class).min(&quot;salary&quot;);
        </pre>


        <h3>More Examples</h3> 

        <table class="ui-panelgrid ui-widget">
            <tr class="ui-widget-header">
                <td class="ui-widget-header" style="text-align: center;" >Query Builder</td>
                <td class="ui-widget-header" style="text-align: center;" >Result Query</td>
                <td class="ui-widget-header" style="text-align: center;" >Result</td>
            </tr>
            <tr>
                <td>
                    <pre name="code" class="java">
                            queryBuilder
                                 .from(Person.class)
                                 .like(&quot;name&quot;, &quot;Maria&quot;)
                                 .orderBy(&quot;name&quot;).getResultList();
                    </pre>

                </td>
                <td>
                    <pre>
FROM Person.class 
WHERE 
name LIKE '%Maria%' 
ORDER BY name
                    </pre>
                </td>
                <td>
                    <p:dataTable var="person" value="#{queryBuilderMB.example1}">
                        <p:column headerText="ID">
                            <h:outputText value="#{person.id}"/>
                        </p:column>
                        <p:column headerText="#{msg['person.name']}">
                            <h:outputText value="#{person.name}"/>
                        </p:column>
                        <p:column headerText="#{msg['person.code']}">
                            <h:outputText value="#{person.code}"/>
                        </p:column>
                        <p:column headerText="#{msg['person.status']}">
                            <h:outputText value="#{person.status.description}"/>
                        </p:column>
                        <p:column headerText="#{msg['person.salary']}">
                            <h:outputText value="#{person.salary}">
                                <f:convertNumber minFractionDigits="2" maxFractionDigits="2"/>
                            </h:outputText>
                        </p:column>
                    </p:dataTable>
                </td>
            </tr>
            <tr>
                <td>
                    <pre name="code" class="java">
                        queryBuilder
                                 .from(Person.class)
                                 .equals(&quot;status&quot;, Status.ACTIVE)
                                 .greaterEqualsThan(&quot;salary&quot;, new BigDecimal(1000))
                                 .orderBy(&quot;code DESC&quot;).getResultList();
                    </pre>

                </td>
                <td>
                    <pre>
FROM Person.class 
WHERE 
status = 'ACTIVE' 
AND salary >= 1000
ORDER BY code DESC
                    </pre>
                </td>
                <td>
                    <p:dataTable var="person" value="#{queryBuilderMB.example2}">
                        <p:column headerText="ID">
                            <h:outputText value="#{person.id}"/>
                        </p:column>
                        <p:column headerText="#{msg['person.name']}">
                            <h:outputText value="#{person.name}"/>
                        </p:column>
                        <p:column headerText="#{msg['person.code']}">
                            <h:outputText value="#{person.code}"/>
                        </p:column>
                        <p:column headerText="#{msg['person.status']}">
                            <h:outputText value="#{person.status.description}"/>
                        </p:column>
                        <p:column headerText="#{msg['person.salary']}">
                            <h:outputText value="#{person.salary}">
                                <f:convertNumber minFractionDigits="2" maxFractionDigits="2"/>
                            </h:outputText>
                        </p:column>
                    </p:dataTable>
                </td>
            </tr>
            <tr>
                <td>
                    <pre name="code" class="java">
                        queryBuilder.from(Person.class).count();
                    </pre>
                </td>
                <td>
                    <pre>
SELECT COUNT(*) FROM Person.class 
                    </pre>
                </td>
                <td>
                    #{queryBuilderMB.countPerson}
                </td>
            </tr>
            <tr>
                <td>
                    <pre name="code" class="java">
                        (BigDecimal) queryBuilder.from(Person.class).sum(&quot;salary&quot;);
                    </pre>
                </td>
                <td>
                    <pre>
SELECT SUM(salary) FROM Person.class 
                    </pre>
                </td>
                <td>
                    #{queryBuilderMB.sumSalary}
                </td>
            </tr>

        </table>




    </ui:define>
</ui:composition>
